import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

public class FruitManageFrame extends JFrame {

	/**
	 * 界面上会出现的控件，作为界面的成员变量定义
	 */
	JTable tbFruit;
	JLabel lblID, lblName, lblPrice, lblAddress;
	JTextField tfID, tfName, tfPrice, tfAddress;
	JButton btnAdd, btnDelete, btnQuery, btnUpdate;
	// 定义滚动布局
	JScrollPane sPane;
	// 定义个盒状
	JPanel lblPanel, etPanel, btnPanel;
	// 定义表格模型
	DefaultTableModel model;

	public FruitManageFrame() {
		this.setSize(500, 600);
		this.setLocation(200, 100);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		// 准备表格要显示的数据模型，并将其设置为表格显示的模型
		initData();
		initView();
		initEvent();

		this.setVisible(true);
	}

	// 设置按钮的点击事件的监听者
	private void initEvent() {
		// 添加按钮的处理
		btnAdd.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				// 准备SQL
				String name, price, address;

				name = tfName.getText();
				price = tfPrice.getText(); // sql语句中所有的都用字符来表示
				address = tfAddress.getText();

				String sql = "INSERT  INTO `fruits`(`name`,`price`,`address`)"
						+ " VALUES (?,?,?)";
				String[] params = new String[] { name, price, address};

				DBUtil db = new DBUtil();
				db.getConn();

				int result = db.update(sql, params);

				db.close();

				if (result > 0) {
					// 添加成功
					JOptionPane
							.showMessageDialog(FruitManageFrame.this, "添加成功");
				} else {
					// 添加失败
					JOptionPane
							.showMessageDialog(FruitManageFrame.this, "添加失败");
				}
				showData();
			}
		});
		// 删除按钮的处理
		btnDelete.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				// 选取待删除的记录

				int[] indexs = tbFruit.getSelectedRows();
				if (indexs.length == 0) {

				}
				if (indexs.length > 1) {

				}
				if (indexs.length == 1) {
					// P202 -10

					int index = indexs[0];// index就是待删除的序号？？？
					DBUtil db = new DBUtil();
					db.getConn();
					// id就是我们待删除的记录的编号
					String id = tbFruit.getValueAt(index, 0).toString();
					String sql = "delete from fruits where id=" + id;

					int result = db.update(sql, null);

					db.close();
					if (result > 0) {
						// 删除成功
						JOptionPane.showMessageDialog(FruitManageFrame.this,
								"删除成功");
					} else {
						// 删除失败
						JOptionPane.showMessageDialog(FruitManageFrame.this,
								"删除失败");
					}
					showData();

				}

			}
		});
		// 查询，根据不同的条件组合，查询结果并显示
		btnQuery.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				// 准备SQL
				String name, price, address;
				
				name = tfName.getText();
				price = tfPrice.getText(); // sql语句中所有的都用字符来表示
				address = tfAddress.getText();

				String sql = "SELECT id,NAME,price,address FROM fruits WHERE 1=1 ";
				
				if (!name.isEmpty()) {
					sql += " and NAME LIKE '%" + name + "%' ";
				}
				if (!price.isEmpty()) {
					sql += " and Price < " + price + " ";
				}
				if (!address.isEmpty()) {
					sql += " and address like '%" + address + "%'";
				}
				System.out.println(sql);
				DBUtil db = new DBUtil();
				ResultSet rs = db.select(sql, null);
				// 处理结果 rs
				try {
					ResultSetMetaData rsmd = rs.getMetaData();
					int columnCount = rsmd.getColumnCount();// 列数
					// 定义一个向量,保存列名
					Vector<String> titles = new Vector<String>();
					for (int i = 1; i <= columnCount; i++) {
						titles.add(rsmd.getColumnLabel(i));
					}
					// 获取查询到的数据添加到model中
					Vector<Vector<String>> data = new Vector<Vector<String>>();
					int rowCount = 0; // 记录的行数
					while (rs.next()) {
						rowCount++;
						Vector<String> rowData = new Vector<String>();
						for (int i = 1; i <= columnCount; i++) {
							rowData.add(rs.getString(i));
						}
						// 将行数据加入data中
						data.add(rowData);
					}
					if (rowCount > 0) {
						model.setDataVector(data, titles);
					} else {
						model.setDataVector(null, titles);
					}

				} catch (SQLException ee) {
					// TODO Auto-generated catch block
					ee.printStackTrace();
				}

			}
		});
		// 更新
		btnUpdate.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				String id, name, price, address;
				id = tfID.getText();
				name = tfName.getText();
				price = tfPrice.getText(); // sql语句中所有的都用字符来表示
				address = tfAddress.getText();

				String sql = "UPDATE fruits SET NAME=?, price =?, address=? WHERE id=?";
				String[] params = new String[] { name, price, address ,id };

				DBUtil db = new DBUtil();
				db.getConn();

				int result = db.update(sql, params);

				db.close();

				if (result > 0) {
					// 修改成功
					JOptionPane
							.showMessageDialog(FruitManageFrame.this, "修改成功");
				} else {
					// 修改失败
					JOptionPane
							.showMessageDialog(FruitManageFrame.this, "修改失败");
				}
				showData();
			}
		});
	}

	private void initData() {
		model = new DefaultTableModel();
		// 这里创建表格控件
		tbFruit = new JTable(model);
		tbFruit.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		// 显示表格中的数据
		showData();
	}

	/**
	 * 根据数据库中的内容，显示数据到tbFruit中
	 */
	private void showData() {

		String sql = "SELECT id,NAME,price,address FROM fruits ";
		DBUtil db = new DBUtil();
		db.getConn();
		ResultSet rs = db.select(sql, null);
		// 处理结果 rs
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();// 列数
			// 定义一个向量,保存列名
			Vector<String> titles = new Vector<String>();
			for (int i = 1; i <= columnCount; i++) {
				titles.add(rsmd.getColumnLabel(i));
			}
			// 获取查询到的数据添加到model中
			Vector<Vector<String>> data = new Vector<Vector<String>>();
			int rowCount = 0; // 记录的行数
			while (rs.next()) {
				rowCount++;
				Vector<String> rowData = new Vector<String>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.add(rs.getString(i));
				}
				// 将行数据加入data中
				data.add(rowData);
			}
			if (rowCount > 0) {
				model.setDataVector(data, titles);
			} else {
				model.setDataVector(null, titles);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		db.close();

	}

	/*
	 * 初始化界面
	 */
	private void initView() {
		// 创建控件

		lblID = new JLabel("ID");
		lblID.setBounds(10, 10, 80, 50);
		lblName = new JLabel("Name");
		lblName.setBounds(100, 10, 80, 50);
		lblPrice = new JLabel("Price");
		lblPrice.setBounds(200, 10, 80, 50);
		lblAddress = new JLabel("Address");
		lblAddress.setBounds(290, 10, 80, 50);

		tfID = new JTextField(5);
		tfID.setBounds(10, 10, 80, 30);
		tfName = new JTextField(10);
		tfName.setBounds(100, 10, 80, 30);
		tfPrice = new JTextField(5);
		tfPrice.setBounds(200, 10, 80, 30);
		tfAddress = new JTextField(10);
		tfAddress.setBounds(290, 10, 80, 30);

		btnAdd = new JButton("Add");
		btnAdd.setBounds(10, 10, 80, 30);
		btnDelete = new JButton("Delete");
		btnDelete.setBounds(100, 10, 80, 30);
		btnQuery = new JButton("Query");
		btnQuery.setBounds(200, 10, 80, 30);
		btnUpdate = new JButton("Update");
		btnUpdate.setBounds(290, 10, 80, 30);

		// 在界面中放置空间
		this.setLayout(null);
		// 定义一个可滚动的pane
		sPane = new JScrollPane();
		sPane.setBounds(50, 50, 400, 300);
		sPane.setViewportView(tbFruit);

		lblPanel = new JPanel();
		lblPanel.setLayout(null);
		lblPanel.add(tfID);
		lblPanel.add(tfName);
		lblPanel.add(tfPrice);
		lblPanel.add(tfAddress);
		lblPanel.setBounds(50, 360, 400, 50);
		lblPanel.add(lblID);
		lblPanel.add(lblName);
		lblPanel.add(lblPrice);
		lblPanel.add(lblAddress);

		etPanel = new JPanel();
		etPanel.setLayout(null);

		etPanel.add(tfID);
		etPanel.add(tfName);
		etPanel.add(tfPrice);
		etPanel.add(tfAddress);
		etPanel.setBounds(50, 420, 400, 50);

		// 按钮的添加
		btnPanel = new JPanel(null);
		btnPanel.add(btnAdd);
		btnPanel.add(btnDelete);
		btnPanel.add(btnQuery);
		btnPanel.add(btnUpdate);
		btnPanel.setBounds(50, 470, 400, 50);

		this.add(btnPanel);
		this.add(lblPanel);
		this.add(etPanel);
		this.add(sPane);

	}

	public static void main(String[] args) {
		new FruitManageFrame();
	}

}
